﻿using Microsoft.VisualBasic.FileIO;
using OfficeOpenXml;
using OfficeOpenXml.ConditionalFormatting.Contracts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace ExcelHelper
{
    public partial class ExcelHelper
    {
        public static List<T> LoadItemsFromExcelFile<T>(string file, string sheet_name)
        {
            ExcelPackage.LicenseContext = LicenseContext.Commercial;
            List<T> items = new List<T>();
            using (var package = new ExcelPackage(new FileInfo(file)))
            {
                int col = 1;
                int row = 1;
                var sheet = package.Workbook.Worksheets[sheet_name];
                //获得field名
                List<string> field_names = new List<string>();
                col = 1;
                row = 1;
                while(true)
                {
                    string? str_field = sheet.Cells[row, col]?.Text;
                    if (string.IsNullOrWhiteSpace(str_field))
                    {
                        break;
                    }
                    field_names.Add(str_field);
                    col++;
                }

                //获得数据
                row = 3;      
                
                while (true)
                {
                    //读一行数据
                    col = 1;
                    int not_empty_count = 0;
                    List<string?> strs = new List<string?>();                    
                    while(col<=field_names.Count)
                    {
                        string? str = sheet.Cells[row, col]?.Text;
                        if (string.IsNullOrWhiteSpace(str))
                        {
                            strs.Add(null);                           
                        }
                        else
                        {
                            strs.Add(str);
                            not_empty_count++;
                        }                       
                        col++;
                    }

                    //如果没有数据，说明到了结束处
                    if (not_empty_count<=0)
                    {
                        break;
                    }

                    //转换为实体数据
                    var type = typeof(T);
                    var item=(T)Activator.CreateInstance(type)!;
                    for(int i=0;i<field_names.Count; i++)
                    {
                        try
                        {
                            //如果数据为空，则跳过
                            if (strs[i] == null)
                            {
                                continue;
                            }

                            //获得field类型
                            var p_info = type.GetProperty(field_names[i]);
                            if (p_info == null)
                            {
                                Serilog.Log.Error($"【ExcelHelper】错误：数据类型{type.Name}不存在{field_names[i]}列");
                                continue;
                            }
                            var p_type = p_info.PropertyType;
                            if (p_type.IsGenericType && p_type.GetGenericTypeDefinition() == typeof(Nullable<>))
                            {
                                p_type = p_type.GetGenericArguments()[0];
                            }
                            //
                            if(p_type==typeof(string))
                            {
                                p_info.SetValue(item, strs[i]);
                            }
                            else if (p_type == typeof(DateOnly)){
                                var obj = DateOnly.Parse(strs[i]);
                                p_info.SetValue(item, obj);
                            }
                            else if(p_type == typeof(DateTime))
                            {
                                var obj = DateTime.Parse(strs[i]);
                                p_info.SetValue(item, obj);
                            }
                            else
                            {
                                var obj = Convert.ChangeType(strs[i], p_type);//不支持DateOnly
                                p_info.SetValue(item, obj);
                            }
                            
                        }
                        catch(Exception e)
                        {
                            Serilog.Log.Error($"【ExcelHelper】获取{type.Name}.{field_names[i]}时出错：{e.Message}");
                        }                       

                    }
                    //
                    items.Add(item);

                    //
                    row++;
                }
            }
            return items;
        }
    }
}
